﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Author: toanvt
/// CreatedDate: 12/12/2011
/// Description: Sản phẩm Provider
/// </summary>
namespace EHTWebsite.Code
{
    public class SanPhamProvider
    {
        /// <summary>
        /// Author: toanvt
        /// CreatedDate: 12/12/2011
        /// Description: Lấy sản phẩm bằng Mã Sản phẩm
        /// </summary>
        /// <param name="SanPhamID">Mã sản phẩm</param>
        /// <returns>Đối tượng sản phẩm</returns>
        public static SanPham GetSanPhamBySanPhamID(Int32 SanPhamID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhamBySanPhamID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@SanPhamID", SanPhamID));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            SanPham theSanPham = null;

            if (ds.Tables[0].Rows.Count > 0)
            {
                theSanPham = new SanPham();
                theSanPham.SanPhamID = Int32.Parse(ds.Tables[0].Rows[0]["SanPhamID"].ToString());
                theSanPham.TenSanPhamVN = ds.Tables[0].Rows[0]["TenSanPhamVN"].ToString();
                theSanPham.TenSanPhamOther = ds.Tables[0].Rows[0]["TenSanPhamOther"].ToString();
                theSanPham.MoTaVN = ds.Tables[0].Rows[0]["MoTaVN"].ToString();
                theSanPham.MoTaOther = ds.Tables[0].Rows[0]["MoTaOther"].ToString();
                theSanPham.DonGia = float.Parse(ds.Tables[0].Rows[0]["DonGia"].ToString());
                theSanPham.SoLuong = Int32.Parse(ds.Tables[0].Rows[0]["SoLuong"].ToString());
                theSanPham.GiamGia = float.Parse(ds.Tables[0].Rows[0]["GiamGia"].ToString());
                theSanPham.HinhAnhUrl = ds.Tables[0].Rows[0]["HinhAnhUrl"].ToString();
                theSanPham.NoiDungVN = ds.Tables[0].Rows[0]["NoiDungVN"].ToString();
                theSanPham.NoiDungOther = ds.Tables[0].Rows[0]["NoiDungOther"].ToString();
                theSanPham.NgayTao = DateTime.Parse(ds.Tables[0].Rows[0]["NgayTao"].ToString());
                theSanPham.DanhMucID = Int32.Parse(ds.Tables[0].Rows[0]["DanhMucID"].ToString());
                theSanPham.NoiBat = Boolean.Parse(ds.Tables[0].Rows[0]["NoiBat"].ToString());
                theSanPham.LuotXem = Int32.Parse(ds.Tables[0].Rows[0]["LuotXem"].ToString());
                theSanPham.SapXep = Int32.Parse(ds.Tables[0].Rows[0]["SapXep"].ToString());
                theSanPham.Publish = Boolean.Parse(ds.Tables[0].Rows[0]["Publish"].ToString());
                theSanPham.SanPhamLienQuan = ds.Tables[0].Rows[0]["SanPhamLienQuan"].ToString();
                theSanPham.MaSku = ds.Tables[0].Rows[0]["MaSku"].ToString();

                theSanPham.SoLuongBan = Int32.Parse(ds.Tables[0].Rows[0]["SoLuongBan"].ToString());
                theSanPham.QuantityPerUnit = ds.Tables[0].Rows[0]["QuantityPerUnit"].ToString();
                theSanPham.PricePerUnit = float.Parse(ds.Tables[0].Rows[0]["PricePerUnit"].ToString());
                theSanPham.UnitsInStock = ds.Tables[0].Rows[0]["UnitsInStock"].ToString();

                theSanPham.SoLuongBan = Int32.Parse(ds.Tables[0].Rows[0]["SoLuongBan"].ToString());

                theSanPham.QuantityPerUnit = ds.Tables[0].Rows[0]["QuantityPerUnit"].ToString();
                theSanPham.PricePerUnit = float.Parse(ds.Tables[0].Rows[0]["PricePerUnit"].ToString());
                theSanPham.UnitsInStock = ds.Tables[0].Rows[0]["UnitsInStock"].ToString();
                

            }

            return theSanPham;
        }

        /// <summary>
        /// Author: toanvt
        /// CreatedDate: 12/12/2011
        /// Description: Lấy danh sách sản phẩm theo mã chuyên mục
        /// </summary>
        /// <param name="DanhMucID">Mã chuyên mục</param>
        /// <returns>Danh sách sản phẩm</returns>
        public static DataSet GetSanPhamByDanhMucID(Int32 DanhMucID, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhamByDanhMucID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@DanhMucID", DanhMucID));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }

        /// <summary>
        /// lay san pham ban chay
        /// </summary>
        /// <param name="DanhMucID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataSet GetSanPhamSellBest(Int32 DanhMucID, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhamsSellBest";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@DanhMucID", DanhMucID));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }


        /// <summary>
        /// lay san pham noi bat
        /// </summary>
        /// <param name="DanhMucID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataSet GetSanPhamHot(Int32 DanhMucID,Boolean Hot, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhamsHot";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@DanhMucID", DanhMucID));
            command.Parameters.Add(new SqlParameter("@Hot", Hot));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }

        public static void UpdateNumberOfOrderBySanPhamID(Int32 SanPhamID, Int32 Sapxep)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "[proc_UpdateNumberOfOrderbySanPhamID]";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@SanPhamID", SanPhamID));
            command.Parameters.Add(new SqlParameter("@Sapxep", Sapxep));
            DataProvider.ExecuteNoneQuery(command);
        }

        /// <summary>
        /// Author: toanvt
        /// CreatedDate: 12/12/2011
        /// Description: Lấy danh sách sản phẩm
        /// </summary>
        /// <returns></returns>
        public static DataSet GetSanPhams()
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhams";
            command.CommandType = CommandType.StoredProcedure;

            return DataProvider.ExecuteDataSet(command);
        }

        /// <summary>
        /// Author: toanvt
        /// CreatedDate: 12/12/2011
        /// Description: Lấy danh sách sản phẩm theo tiêu chí tìm kiếm và có phân trang
        /// </summary>
        /// <param name="theSanPham">Đối tượng sản phẩm chứa tiêu chí tìm kiếm</param>
        /// <param name="pageIndex">Trang hiện tại</param>
        /// <param name="pageSize">Số record trên một trang</param>
        /// <param name="totalRecord">Tổng số record</param>
        /// <returns>Danh sách sản phẩm</returns>
        public static DataSet GetSanPhamsByFilter(SanPham theSanPham, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhamsByFilter";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@TenSanPhamVN", theSanPham.TenSanPhamVN));
            command.Parameters.Add(new SqlParameter("@TenSanPhamOther", theSanPham.TenSanPhamOther));            
            command.Parameters.Add(new SqlParameter("@DanhMucID", theSanPham.DanhMucID));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);

            return ds;
        }
        public static DataSet GetSanPhamsByFilterT(SanPham theSanPham, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetSanPhamsByFilterT";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@TenSanPhamVN", theSanPham.TenSanPhamVN));
            command.Parameters.Add(new SqlParameter("@TenSanPhamOther", theSanPham.TenSanPhamOther));
            command.Parameters.Add(new SqlParameter("@DanhMucID", theSanPham.DanhMucID));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);

            return ds;
        }

        /// <summary>
        /// Author: toanvt
        /// CreatedDate: 12/12/2011
        /// Description: Inser, Update sản phẩm
        /// </summary>
        /// <param name="theSanPham">Đối tượng sản phẩm</param>
        /// <param name="isHot">Trạng thái sản phẩm Hot hay không</param>
        /// <param name="isAdd">Trạng thái insert hay update</param>
        public static Int32 IUSanPham(SanPham theSanPham, Boolean isAdd)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_IUSanPham";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter theSanPhamParameter = new SqlParameter();

            theSanPhamParameter.ParameterName = "@SanPhamIDOut";
            theSanPhamParameter.Direction = ParameterDirection.Output;
            theSanPhamParameter.DbType = DbType.Int32;

            command.Parameters.Add(theSanPhamParameter);
            command.Parameters.Add(new SqlParameter("@SanPhamID", theSanPham.SanPhamID));
            command.Parameters.Add(new SqlParameter("@TenSanPhamVN", theSanPham.TenSanPhamVN));
            command.Parameters.Add(new SqlParameter("@TenSanPhamOther", theSanPham.TenSanPhamOther));
            command.Parameters.Add(new SqlParameter("@MoTaVN", theSanPham.MoTaVN));
            command.Parameters.Add(new SqlParameter("@MoTaOther", theSanPham.MoTaOther));
            command.Parameters.Add(new SqlParameter("@DonGia", theSanPham.DonGia));
            command.Parameters.Add(new SqlParameter("@SoLuong", theSanPham.SoLuong));
            command.Parameters.Add(new SqlParameter("@GiamGia", theSanPham.GiamGia));
            command.Parameters.Add(new SqlParameter("@HinhAnhUrl", theSanPham.HinhAnhUrl));
            command.Parameters.Add(new SqlParameter("@NoiDungVN", theSanPham.NoiDungVN));
            command.Parameters.Add(new SqlParameter("@NoiDungOther", theSanPham.NoiDungOther));
            command.Parameters.Add(new SqlParameter("@DanhMucID", theSanPham.DanhMucID));
            command.Parameters.Add(new SqlParameter("@SapXep", theSanPham.SapXep));
            command.Parameters.Add(new SqlParameter("@NoiBat", theSanPham.NoiBat));
            command.Parameters.Add(new SqlParameter("@SanPhamLienQuan", theSanPham.SanPhamLienQuan));
            command.Parameters.Add(new SqlParameter("@MaSku", theSanPham.MaSku));
            command.Parameters.Add(new SqlParameter("@IsAdd", isAdd));
            DataProvider.ExecuteNoneQuery(command);

            return Int32.Parse(command.Parameters["@SanPhamIDOut"].Value.ToString());
        }

        /// <summary>
        /// Author: toanvt
        /// CreatedDate: 12/12/2011
        /// Description: Xóa sản phẩm theo mã sản phẩm
        /// </summary>
        /// <param name="SanPhamID">Mã sản phẩm</param>
        public static void DeleteSanPhamBySanPhamID(Int32 SanPhamID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_DeleteSanPhamBySanPhamID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@SanPhamID", SanPhamID));
            DataProvider.ExecuteNoneQuery(command);
        }

        /// <summary>
        /// ham lay top 4 san pham
        /// </summary>
        /// <returns></returns>
        
        public static DataSet getTopSanPham()
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetTopProduct";
            command.CommandType = CommandType.StoredProcedure;           
            DataSet ds = DataProvider.ExecuteDataSet(command);

            return ds;
        }

        public static void ChangePublish(Int32 SanPhamID, Boolean publish)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "[proc_ChangePublishBySanPhamID]";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@SanPhamID", SanPhamID));
            command.Parameters.Add(new SqlParameter("@Publish", publish));
            DataProvider.ExecuteNoneQuery(command);
        }

        public static DataSet Check_Product(String MaSku, String TenSanPhamVN, String TenSanPhamOther)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "[proc_CheckProduct]";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@MaSku", MaSku));
            command.Parameters.Add(new SqlParameter("@TenSanPhamVN", TenSanPhamVN));
            command.Parameters.Add(new SqlParameter("@TenSanPhamOther", TenSanPhamOther));            
            DataSet ds = DataProvider.ExecuteDataSet(command);

            return ds;
        }
    }
}